using mapshiftApplication.Helpers;
using Microsoft.AspNetCore.Cors;
using Microsoft.AspNetCore.Mvc;
using Oracle.ManagedDataAccess.Client;

namespace mapshiftApplication.Controllers;


[ApiController]
[EnableCors("AllowFrontend")]
[Route("api/normal")]
public class UntestedBinController : ControllerBase
{
        private readonly IOracleConnectionFactory _oracleConnectionFactory;

        public UntestedBinController(IOracleConnectionFactory oracleConnectionFactory)
        {
            _oracleConnectionFactory = oracleConnectionFactory;
        }


        [HttpPost]
        [Route("add")]
        public async Task<IActionResult> AddRecord([FromBody] MmsRecord3 record)
        {
            await using var oracleConnection = _oracleConnectionFactory.GetConnection("DefaultConnection");
            await oracleConnection.OpenAsync();
            
            string newMmsId = Guid.NewGuid().ToString();

            await using var command = new OracleCommand(
                "INSERT INTO MMS_UNTESTED_BIN  (MUB_ID,MUB_CUST,MUB_DEVICE,MUB_BIN,MUB_REMARK ) VALUES (:id, :cust, :dev, :bin, :rm)",
                oracleConnection);
            command.Parameters.Add(new OracleParameter("id", newMmsId));
            command.Parameters.Add(new OracleParameter("cust", record.Mub_cust));
            command.Parameters.Add(new OracleParameter("dev", record.Mub_device));
            command.Parameters.Add(new OracleParameter("bin", record.Mub_bin));
            command.Parameters.Add(new OracleParameter("rm", record.Mub_remark ?? (object)DBNull.Value));


            int rows = await command.ExecuteNonQueryAsync();
            return rows > 0 ? Ok(new { message = "Record added successfully",code = "200" }) : BadRequest("Insert failed");
        }
        
        [HttpGet]
        [Route("query")]
        public async Task<IActionResult> GetRecords([FromQuery] int page = 1, [FromQuery] int pageSize = 10, [FromQuery] string cust = "", [FromQuery] string device = "")
        {
            
            await using var oracleConnection = _oracleConnectionFactory.GetConnection("DefaultConnection");
            await oracleConnection.OpenAsync();
            string sql = "SELECT * FROM MMS_UNTESTED_BIN ";
            
            string countSql = "SELECT COUNT(*) FROM MMS_UNTESTED_BIN WHERE 1=1";
            if (!string.IsNullOrEmpty(cust)) countSql += " AND mub_cust = :cust";
            if (!string.IsNullOrEmpty(device)) countSql += " AND mub_device = :device";
            using var countCommand = new OracleCommand(countSql, oracleConnection);
            if (!string.IsNullOrEmpty(cust)) countCommand.Parameters.Add(new OracleParameter("cust", cust));
            if (!string.IsNullOrEmpty(device)) countCommand.Parameters.Add(new OracleParameter("device", device));

            int totalRecords = Convert.ToInt32(await countCommand.ExecuteScalarAsync());
            
            List<OracleParameter> parameters = new List<OracleParameter>();
            List<string> conditions = new List<string>();
            if (!string.IsNullOrEmpty(cust))
            {
                conditions.Add("mub_cust = :cust");
                parameters.Add(new OracleParameter("cust", cust));
            }
            
            if (!string.IsNullOrEmpty(device))
            {
                conditions.Add("mub_device = :device");
                parameters.Add(new OracleParameter("device", device));
            }
            
            if (conditions.Count > 0)
            {
                sql += " WHERE " + string.Join(" AND ", conditions);
            }
            sql += " OFFSET :offset ROWS FETCH NEXT :pageSize ROWS ONLY";
            parameters.Add(new OracleParameter("offset", (page - 1) * pageSize));
            parameters.Add(new OracleParameter("pageSize", pageSize));

            await using var command = new OracleCommand(sql, oracleConnection);
            command.Parameters.AddRange(parameters.ToArray());

            await using var reader = await command.ExecuteReaderAsync();
            var records = new List<MmsRecord3>();
            while (await reader.ReadAsync())
            {
                records.Add(new MmsRecord3
                {
                    Mub_id = reader["mub_id"].ToString()!,
                    Mub_cust = reader["mub_cust"].ToString()!,
                    Mub_device = reader["mub_device"].ToString()!,
                    Mub_bin = reader["mub_bin"].ToString()!,
                    Mub_remark = reader["mub_remark"].ToString()!
                });
            }
            return Ok(new { items = records, total = totalRecords });
        }
    
        
        [HttpGet]
        [Route("delete/{id}")]
        public async Task<IActionResult> DeleteRecord(string id)
        {
            await using var oracleConnection = _oracleConnectionFactory.GetConnection("DefaultConnection");
            await oracleConnection.OpenAsync();
    
            string sql = "DELETE FROM MMS_UNTESTED_BIN WHERE MUB_ID = :id";

            await using var command = new OracleCommand(sql, oracleConnection);
            command.Parameters.Add(new OracleParameter("id", id));

            int rowsAffected = await command.ExecuteNonQueryAsync();
        
            return rowsAffected > 0 ? Ok("Record deleted successfully.") : NotFound("Record not found.");
        }
        
        [HttpPost]
        [Route("edit")]
        public async Task<IActionResult> EditRecord( [FromBody] MmsRecord3 record)
        {
            await using var oracleConnection = _oracleConnectionFactory.GetConnection("DefaultConnection");
            await oracleConnection.OpenAsync();
            
            string sql = $@"
                UPDATE MMS_UNTESTED_BIN 
                SET mub_cust = '{record.Mub_cust}', 
                    mub_device = '{record.Mub_device}', 
                    mub_bin = '{record.Mub_bin}', 
                    mub_remark = '{record.Mub_remark}'
                WHERE mub_id = '{record.Mub_id}'";

            LogHelper.Info("Final Executing SQL: " + sql);


            await using var command = new OracleCommand(sql, oracleConnection);
            int rows = await command.ExecuteNonQueryAsync();
                
            return rows > 0 ? Ok("Record update successfully.") : NotFound("Record not found.");
        }
    
        
        
        
        
         [HttpPost]
        [Route("add/testname")]
        public async Task<IActionResult> AddRecordTestName([FromBody] MmsRecord4 record)
        {
            await using var oracleConnection = _oracleConnectionFactory.GetConnection("DefaultConnection");
            await oracleConnection.OpenAsync();
            
            string newMmsId = Guid.NewGuid().ToString();

            await using var command = new OracleCommand(
                "INSERT INTO MMS_TEST_NAME  (MTN_ID,MTN_CUST,MTN_DEVICE,MTN_TEST_NAME,MTN_CP,MTN_CPK) VALUES (:id, :cust, :dev, :testname,:cp,:cpk)",
                oracleConnection);
            command.Parameters.Add(new OracleParameter("id", newMmsId));
            command.Parameters.Add(new OracleParameter("cust", record.Mtn_cust));
            command.Parameters.Add(new OracleParameter("dev", record.Mtn_device));
            command.Parameters.Add(new OracleParameter("testname", record.Mtn_test_name));
            command.Parameters.Add(new OracleParameter("cp", record.Mtn_cp));
            command.Parameters.Add(new OracleParameter("cp", record.Mtn_cpk));
            int rows = await command.ExecuteNonQueryAsync();
            return rows > 0 ? Ok(new { message = "Record added successfully",code = "200" }) : BadRequest("Insert failed");
        }
        
        [HttpGet]
        [Route("query/testname")]
        public async Task<IActionResult> GetRecordsTestName([FromQuery] int page = 1, [FromQuery] int pageSize = 10, [FromQuery] string cust = "", [FromQuery] string device = "")
        {
            await using var oracleConnection = _oracleConnectionFactory.GetConnection("DefaultConnection");
            await oracleConnection.OpenAsync();
            string sql = "SELECT * FROM MMS_TEST_NAME ";
            
            string countSql = "SELECT COUNT(*) FROM MMS_TEST_NAME WHERE 1=1";
            if (!string.IsNullOrEmpty(cust)) countSql += " AND mtn_cust = :cust";
            if (!string.IsNullOrEmpty(device)) countSql += " AND mtn_device = :device";
            using var countCommand = new OracleCommand(countSql, oracleConnection);
            if (!string.IsNullOrEmpty(cust)) countCommand.Parameters.Add(new OracleParameter("cust", cust));
            if (!string.IsNullOrEmpty(device)) countCommand.Parameters.Add(new OracleParameter("device", device));

            int totalRecords = Convert.ToInt32(await countCommand.ExecuteScalarAsync());
            
            List<OracleParameter> parameters = new List<OracleParameter>();
            List<string> conditions = new List<string>();
            if (!string.IsNullOrEmpty(cust))
            {
                conditions.Add("mtn_cust = :cust");
                parameters.Add(new OracleParameter("cust", cust));
            }
            
            if (!string.IsNullOrEmpty(device))
            {
                conditions.Add("mtn_device = :device");
                parameters.Add(new OracleParameter("device", device));
            }
            
            if (conditions.Count > 0)
            {
                sql += " WHERE " + string.Join(" AND ", conditions);
            }
            sql += " OFFSET :offset ROWS FETCH NEXT :pageSize ROWS ONLY";
            parameters.Add(new OracleParameter("offset", (page - 1) * pageSize));
            parameters.Add(new OracleParameter("pageSize", pageSize));

            await using var command = new OracleCommand(sql, oracleConnection);
            command.Parameters.AddRange(parameters.ToArray());

            await using var reader = await command.ExecuteReaderAsync();
            var records = new List<MmsRecord4>();
            while (await reader.ReadAsync())
            {
                records.Add(new MmsRecord4
                {
                    Mtn_id = reader["mtn_id"].ToString()!,
                    Mtn_cust = reader["mtn_cust"].ToString()!,
                    Mtn_device = reader["mtn_device"].ToString()!,
                    Mtn_test_name = reader["mtn_test_name"].ToString()!,
                    Mtn_cp = reader["mtn_cp"].ToString()!,
                    Mtn_cpk = reader["mtn_cpk"].ToString()!
                });
            }
            return Ok(new { items = records, total = totalRecords });
        }
    
        
        [HttpGet]
        [Route("delete/testname/{id}")]
        public async Task<IActionResult> DeleteRecordTestName(string id)
        {
            await using var oracleConnection = _oracleConnectionFactory.GetConnection("DefaultConnection");
            await oracleConnection.OpenAsync();
    
            string sql = "DELETE FROM MMS_TEST_NAME WHERE MTN_ID = :id";

            await using var command = new OracleCommand(sql, oracleConnection);
            command.Parameters.Add(new OracleParameter("id", id));

            int rowsAffected = await command.ExecuteNonQueryAsync();
        
            return rowsAffected > 0 ? Ok("Record deleted successfully.") : NotFound("Record not found.");
        }
        
        [HttpPost]
        [Route("edit/testname")]
        public async Task<IActionResult> EditRecordTestName( [FromBody] MmsRecord4 record)
        {
            await using var oracleConnection = _oracleConnectionFactory.GetConnection("DefaultConnection");
            await oracleConnection.OpenAsync();
            
            string sql = $@"
                UPDATE MMS_TEST_NAME 
                SET mtn_cust = '{record.Mtn_cust}', 
                    mtn_device = '{record.Mtn_device}', 
                    mtn_test_name = '{record.Mtn_test_name}',
                    mtn_cp = '{record.Mtn_cp}',
                    mtn_cpk = '{record.Mtn_cpk}'
                WHERE mtn_id = '{record.Mtn_id}'";

            LogHelper.Info("Final Executing SQL: " + sql);


            await using var command = new OracleCommand(sql, oracleConnection);
            int rows = await command.ExecuteNonQueryAsync();
                
            return rows > 0 ? Ok("Record update successfully.") : NotFound("Record not found.");
        }
    
    public class MmsRecord3
    {
        
        public  string? Mub_id { get; set; }
        public string?  Mub_cust { get; set; }
        public string?  Mub_device { get; set; }
        
        public string?  Mub_bin { get; set; }
        
        public string?  Mub_remark { get; set; }
    }
    
    
    public class MmsRecord4
    {
        
        public  string? Mtn_id { get; set; }
        public string?  Mtn_cust { get; set; }
        public string?  Mtn_device { get; set; }
        
        public string?  Mtn_test_name { get; set; }
        public string?  Mtn_cp { get; set; }
        
        public string? Mtn_cpk { get; set; }
    }
}